/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dados.SQLConnection;

import dados.Cripto;
import dados.Medico;
import dados.MedicoJaExisteException;
import dados.MedicoNaoExisteException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.GregorianCalendar;

/**
 *
 * @author rmb
 */
public class SQLMedicos {
    
    private Connection connection;
    
    public SQLMedicos(Connection con) {
        connection = con;
    }
    
    public String getNomeEspecialidade(String cod_esp) {                
        
        String espQuer = "select descricao from especialidades where cod_especialidade = ?";
        String descr = null;
        
        try {
            PreparedStatement ps = connection.prepareCall(espQuer);
            ps.setString(1, cod_esp);
            ResultSet rs = ps.executeQuery();
            
            while(rs.next())
                descr = rs.getString("descricao");
            
        }
        catch(SQLException e) {}
        
        return descr;
                    
    }

    
    
    public void insereMedico(Medico m) throws MedicoJaExisteException {
        
        String insmedProc = "{call InserirMedico(?,?,?,?,?,?,?,?,?)}";
        String medQuer = "select count(*) from medicos where bi = ?";     
        try{
            PreparedStatement ps = connection.prepareStatement(medQuer);
            ps.setString(1,m.getBi());
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt(1);
            
            if(act == 1)
                throw new MedicoJaExisteException();
            
            CallableStatement cs = connection.prepareCall(insmedProc);
            cs.setString(1, m.getNome());
            cs.setString(2, m.getBi());
            cs.setString(3,m.getMorada());
            cs.setString(4,m.getCod_postal());
            cs.setString(5,m.getLocalidade());
            cs.setDate(6, new Date(m.getDataNasc().getTimeInMillis()));
            cs.setInt(7, m.getCod_especialidade());
            cs.setString(8,m.getContacto());
            cs.setString(9,m.getPassword());
            
            cs.execute();
            connection.commit();
        }
        catch(SQLException e) {}                
        
        
    }
    
    public void eliminaMedico(String cod) throws MedicoNaoExisteException {
        
        String delmedProc = "{call EliminarMedico(?)}";             
        String medActQuer = "select ativo from medicos where cod_medico = ?";     
        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
                                                
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}
        
    }
    
    public void editarMedicoBI(String cod, int novoBi) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_BI(?,?)}";             
        String medActQuer = "select ativo from medicos where cod_medico = ?";    
        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
            
                                    
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.setInt(2, novoBi);
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}       
        
        
    }
    
    
    public void editarMedicoCod_Postal(String cod, String cod_postal, String localidade) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_Cod_Postal(?,?,?)}";
        String medActQuer = "select ativo from medicos where cod_medico = ?";
        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
           
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.setString(2, cod_postal);
            cs.setString(3, localidade);
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}       
        
        
    }
    
    
    
    public void editarMedicoData_Nasc(String cod, GregorianCalendar novaData) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_Cod_Postal(?,?)}";           
        String medActQuer = "select ativo from medicos where cod_medico = ?";

        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
                       
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.setDate(2, new Date(novaData.getTimeInMillis()));                        
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}       
        
        
    }
        
    public void editarMedicoEspecialidade(String cod_med,String cod_esp) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_Especialidade(?,?)}";            
        String medActQuer = "select ativo from medicos where cod_medico = ?";     

        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setInt(1, Integer.parseInt(cod_med));
            ResultSet rs = ps.executeQuery();
            int act = 0;            
            
            while(rs.next())
                act = rs.getInt("ativo");
                
            if(act == 0)
                throw new MedicoNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod_med));
            cs.setInt(2, Integer.parseInt(cod_esp));            
            cs.execute();
            
            connection.commit();
            
        }
        catch(SQLException e){} 
        
        
    }
    
    
    public void editarMedicoMorada(String cod, String morada) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_Morada(?,?)}";            
        String medActQuer = "select ativo from medicos where cod_medico = ?";

        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
            
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.setString(2, morada);            
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}       
                
    }
    
    
    public void editarMedicoNome(String cod, String nome) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_Morada(?,?)}";               
        String medActQuer = "select ativo from medicos where cod_medico = ?";
        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
                        
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.setString(2, nome);            
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}       
        
        
    }
    
    public void editarMedicoPassword(String cod, String pass) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_Password(?,?)}";
        String medActQuer = "select ativo from medicos where cod_medico = ?";

        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
                       
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            
            Cripto c = new Cripto(pass);
            
            cs.setString(2, c.encrypt());            
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}       
        
        
    }
    
    public void editarMedicoContacto(String cod, String contacto) throws MedicoNaoExisteException {
        
        String delmedProc = "{call Editar_Medico_Telefone(?,?)}";
        String medActQuer = "select ativo from medicos where cod_medico = ?";
        
        try{
            PreparedStatement ps = connection.prepareStatement(medActQuer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            int act = 0;
            
            while(rs.next())
                act = rs.getInt("ativo");
            
            if(act == 0)
                throw new MedicoNaoExisteException();
                        
            CallableStatement cs = connection.prepareCall(delmedProc);
            cs.setInt(1, Integer.parseInt(cod));
            cs.setString(2, contacto);            
            cs.execute();
            connection.commit();
            
        }
        catch(SQLException e){}       
        
        
    }
    
    public String getMedicoPassword (String cod) throws MedicoNaoExisteException {
        
       String passQuer = "select password from medicos where cod_medico = ?" ;
       String medActQuer = "select ativo from medicos where cod_medico = ?" ;        
       String password = null;
       try {           
           PreparedStatement ps = connection.prepareStatement(medActQuer);
           ps.setInt(1, Integer.parseInt(cod));
           ResultSet rs = ps.executeQuery();                                                
           int act = 0;
           
           while(rs.next())
                act = rs.getInt("ativo");
           
           rs.close();
           ps.close();
           if(act == 0)
               throw new MedicoNaoExisteException();
           
           PreparedStatement ps2 = connection.prepareStatement(passQuer);
           ps2.setInt(1, Integer.parseInt(cod));
           ResultSet rs2 = ps2.executeQuery();
           
           while(rs2.next()) {
             password = rs2.getString("password");             
           }
           
           rs2.close();
           ps2.close();
       }
       catch(SQLException e) {}
       
       return password;
     
    }
    
    
    public ArrayList<Medico> getMedicosNome (String nome) {
        
        ArrayList<Medico> meds = new ArrayList<Medico>();
        String quer = "select count(*) from medicos where ativo = 1 and nome like ?";
        String quer2 = "select * from medicos where ativo = 1 and nome like ?";
        
        try {
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1,"%"+nome+"%");
            ResultSet rs = ps.executeQuery();
            int count = 0;
            
            while(rs.next()) {
                count = rs.getInt(1);
            }
            
            rs.close();
            ps.close();
            
            if(count < 20) {
                
                PreparedStatement ps2 = connection.prepareStatement(quer2);
                ps2.setString(1, "%"+nome+"%");
                ResultSet rs2 = ps2.executeQuery();                         
                GregorianCalendar g = new GregorianCalendar();
                
                while(rs2.next()) {                                         
                    g.setTime(rs2.getDate("data_nascimento"));                                         
                                        
                    meds.add(new Medico(rs2.getString("nome"), rs2.getString("bi"), rs2.getString("cod_medico"), 
                            rs2.getInt("cod_especialidade"), rs2.getString("morada"), getLocalidadeDescr(rs2.getString("cod_postal")),
                                    rs2.getString("cod_postal"), g, rs2.getString("telefone"), rs2.getString("password")));
                    
                }                            
            }            
        }
        catch(SQLException e) {
            
        }        
        return meds;
    }
    
    public ArrayList<Medico> getMedicosCod (String cod) {
        
        ArrayList<Medico> meds = new ArrayList<Medico>();
        String quer = "select count(*) from medicos where ativo = 1 and cod_medico like ?";
        String quer2 = "select * from medicos where ativo = 1 and cod_medico like ?";
        
        try {
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1,"%"+cod+"%");
            ResultSet rs = ps.executeQuery();
            int count = 0;
            
            while(rs.next()) {
                count = rs.getInt(1);
            }
            
            rs.close();
            ps.close();
            
            if(count < 20) {
                
                PreparedStatement ps2 = connection.prepareStatement(quer2);
                ps2.setString(1, "%"+cod+"%");
                ResultSet rs2 = ps2.executeQuery();                         
                GregorianCalendar g = new GregorianCalendar();
                
                while(rs2.next()) {                                         
                    g.setTime(rs2.getDate("data_nascimento"));                                         
                                        
                    meds.add(new Medico(rs2.getString("nome"), rs2.getString("bi"), rs2.getString("cod_medico"), 
                            rs2.getInt("cod_especialidade"), rs2.getString("morada"), getLocalidadeDescr(rs2.getString("cod_postal")),
                                    rs2.getString("cod_postal"), g, rs2.getString("telefone"), rs2.getString("password")));
                    
                }                            
            }            
        }
        catch(SQLException e) {
            
        }        
        return meds;
    }
    
    private String getLocalidadeDescr (String cod_postal) {
        
        String descr = null;
        String quer = "select descricao from cod_postal where cod_postal = ?";
        
        try {
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1, cod_postal);
            ResultSet rs = ps.executeQuery();
                                    
            while(rs.next())
                descr = rs.getString("descricao");
            
        }
        catch(SQLException e) {}
        
        return descr;        
    }
    
    public Medico getMedicoCod(String cod) {
        
        Medico med = null;
        
        String quer = "select * from medicos where cod_medico = ?";
        
        try{
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1, cod);
            ResultSet rs = ps.executeQuery();
            GregorianCalendar g = new GregorianCalendar();
            while(rs.next()) {                                         
                    g.setTime(rs.getDate("data_nascimento"));                                         
                    
                    med = new Medico(rs.getString("nome"), rs.getString("bi"), rs.getString("cod_medico"), 
                            rs.getInt("cod_especialidade"), rs.getString("morada"), getLocalidadeDescr(rs.getString("cod_postal")),
                                    rs.getString("cod_postal"), g, rs.getString("telefone"), rs.getString("password"));                                        
                }          
            
            
        }
        catch(SQLException e) {}
        
        
        return med;
    }
    
}
